import os
import smtplib
from email import encoders
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

import pandas as pd
from fastapi import APIRouter, Request
from openpyxl.styles import Alignment, Font
from openpyxl.workbook import Workbook

from server.module_chat.business.flfj.service.flfj_service import FlfjService
from server.settings import Settings

flfjController = APIRouter(prefix="/business/flfj", tags=["业务-分类分级"])


@flfjController.get("/buildGraph")
async def build_graph(request: Request):
    """
    构建分类分级知识图谱
    """
    await FlfjService.build_graph()
    return {"code": 200, "msg": "success", "data": {}}


@flfjController.get("/send_bs")
async def send_bs(request: Request):
    bs_file_path = 'F://数据安全分类分级商机.xlsx'
    parse_bs_excel(bs_file_path)

    email_file_path = 'F://各省销售联系邮箱.xlsx'
    sheet_list = parse_email_excel(email_file_path)

    for sheet_data in sheet_list:
        email_data_list = sheet_data['data']
        for email_data in email_data_list:
            region = email_data["region"]
            province = email_data["province"]
            emails = email_data["emails"]
            # 按逗号分隔province为列表
            province_list = province.split('、')
            attachment_paths = []
            receiver_emails = emails
            if '全国' in province_list:
                attachment_paths.append(bs_file_path)
            else:
                for province in province_list:
                    # 判断文件是否存在
                    # temp_path = str(Path(f'{Settings.upload_settings.UPLOAD_PATH}/{province}-数据分类分级商机.xlsx').resolve())
                    if os.path.exists(f'{Settings.upload_settings.UPLOAD_PATH}/{province}-数据分类分级商机.xlsx'):
                        attachment_paths.append(
                            f'{Settings.upload_settings.UPLOAD_PATH}/{province}-数据分类分级商机.xlsx')
            # 判断attachment_paths是否为空
            if attachment_paths:
                send_email(attachment_paths, receiver_emails)


def parse_bs_excel(bs_file_path):
    # 读取Excel文件
    df = pd.read_excel(bs_file_path)

    # 动态获取省份列的索引
    province_column_name = '省份'
    if province_column_name not in df.columns:
        raise ValueError(f"没有找到名为 '{province_column_name}' 的列")

    # 将省份列移到最前面
    columns = list(df.columns)
    columns.insert(0, columns.pop(columns.index(province_column_name)))
    df = df[columns].sort_values(by='发布时间', ascending=False)

    # 按省份拆分DataFrame
    provinces = df[province_column_name].dropna().unique()
    for province in provinces:
        province_df = df[df[province_column_name] == province]

        # 创建一个新的Workbook对象
        wb = Workbook()

        # 商机信息表
        business_opportunities = province_df.copy()
        ws1 = wb.create_sheet(title='商机信息')

        # 写入表头
        for col_num, column_title in enumerate(business_opportunities.columns, 1):
            header1 = ws1.cell(row=1, column=col_num, value=column_title)
            header1.alignment = Alignment(horizontal='center', vertical='center')
            header1.font = Font(name='Arial', size=12, bold=True)

        # 写入数据
        for r_idx, row in enumerate(business_opportunities.itertuples(index=False), start=2):
            for col_num, value in enumerate(row, 1):
                cell1 = ws1.cell(row=r_idx, column=col_num, value=value)
                cell1.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
                cell1.font = Font(name='Arial', size=12)
                # 设置单元格宽度
                ws1.column_dimensions[chr(64 + col_num)].width = 32

        # 合并省份单元格
        province_col_idx = 1  # 现在省份列为第一列
        start_row = 2  # 考虑到标题行
        end_row = len(business_opportunities) + 1
        ws1.merge_cells(start_row=start_row, start_column=province_col_idx, end_row=end_row,
                        end_column=province_col_idx)
        cell = ws1.cell(row=start_row, column=province_col_idx, value=province)
        cell.alignment = Alignment(horizontal='center', vertical='center')

        # 招投标信息表
        bidding_info = province_df[['招标公司', '产品', '中标公司']].copy()
        ws2 = wb.create_sheet(title='招投标信息')

        # 写入表头
        for col_num, column_title in enumerate(bidding_info.columns, 1):
            header2 = ws2.cell(row=1, column=col_num, value=column_title)
            header2.alignment = Alignment(horizontal='center', vertical='center')
            header2.font = Font(name='Arial', size=12, bold=True)

        # 写入数据
        current_company = None
        start_row = 2  # 考虑到标题行
        for i, row in enumerate(bidding_info.itertuples(index=False), start=2):
            for col_num, value in enumerate(row, 1):
                cell2 = ws2.cell(row=i, column=col_num, value=value)
                cell2.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
                cell2.font = Font(name='Arial', size=12)
                # 设置单元格宽度
                ws2.column_dimensions[chr(64 + col_num)].width = 32

            if current_company != row.招标公司:
                if current_company is not None:
                    ws2.merge_cells(start_row=start_row, start_column=1, end_row=i - 1, end_column=1)
                    cell = ws2.cell(row=start_row, column=1, value=current_company)
                    cell.alignment = Alignment(horizontal='center', vertical='center')
                current_company = row.招标公司
                start_row = i

        # 处理最后一组合并·
        ws2.merge_cells(start_row=start_row, start_column=1, end_row=len(bidding_info) + 1, end_column=1)
        cell = ws2.cell(row=start_row, column=1, value=current_company)
        cell.alignment = Alignment(horizontal='center', vertical='center')

        # 删除默认创建的第一个空sheet
        wb.remove(wb.worksheets[0])

        # 保存文件
        output_file_name = f'{Settings.upload_settings.UPLOAD_PATH}/{province}-数据分类分级商机.xlsx'
        wb.save(output_file_name)

    print("所有省份的数据已成功拆分并保存为单独的Excel文件。")


def parse_email_excel(email_file_path):
    # 读取Excel文件
    xls = pd.ExcelFile(email_file_path)

    # 初始化结果列表
    result = []

    # 遍历每个sheet
    for sheet_name in xls.sheet_names:
        df = xls.parse(sheet_name)

        # 初始化当前sheet的数据
        sheet_data = {
            "sheetName": sheet_name,
            "data": []
        }

        # 遍历每一行
        current_region = None
        current_province = None
        emails = []

        for index, row in df.iterrows():
            # 检查大区是否为空，不为空则更新当前大区
            if pd.notna(row['大区']):
                if current_region:
                    sheet_data["data"].append({
                        "region": current_region,
                        "province": current_province,
                        "emails": emails
                    })
                    emails = []
                current_region = row['大区']

            # 检查省份是否为空，不为空则更新当前省份
            if pd.notna(row['省份']):
                current_province = row['省份']

            # 添加邮箱到列表
            if pd.notna(row['邮箱']):
                emails.append(row['邮箱'])

        # 添加最后一个大区的数据
        if current_region:
            sheet_data["data"].append({
                "region": current_region,
                "province": current_province,
                "emails": emails
            })

        # 将当前sheet的数据添加到结果中
        result.append(sheet_data)

    # 输出结果
    print(result)
    return result


def send_email(attachment_paths, receiver_emails):
    try:
        subject = "数据安全分类分级的招投标信息和代理商信息"
        body = """
        各位领导，同事，大家好：
            以下是数据安全分类分级的招投标信息和代理商信息，请查收，谢谢！
        """
        # 抄送人邮箱列表
        cc_emails = ['chenghong@act-telecom.com']
        msg = MIMEMultipart()
        msg['Subject'] = subject
        msg['From'] = 'liuyanchang@act-telecom.com'
        msg['To'] = ', '.join(receiver_emails)
        msg['Cc'] = ', '.join(cc_emails)
        smtp_server = 'smtp.exmail.qq.com'
        smtp_port = 465
        smtp_username = 'liuyanchang@act-telecom.com'
        smtp_password = '6vPk2R3B75MWmgDJ'
        # 添加邮件正文
        msg.attach(MIMEText(body, 'plain'))

        # 循环遍历所有附件路径并添加到邮件中
        for attachment_path in attachment_paths:
            if os.path.isfile(attachment_path):
                # 打开并读取附件文件
                attachment = open(attachment_path, "rb")

                # 创建MIMEBase对象，并设置Content-Disposition
                part = MIMEBase('application', 'octet-stream')
                part.set_payload(attachment.read())
                encoders.encode_base64(part)
                temp_file_name = os.path.basename(attachment_path)
                part.add_header('Content-Disposition', 'attachment', filename=temp_file_name)

                # 将附件添加到消息
                msg.attach(part)
                attachment.close()
        with smtplib.SMTP_SSL(smtp_server, smtp_port) as server:
            server.login(smtp_username, smtp_password)
            server.send_message(msg)
        print(f"Email sent to {receiver_emails} successfully.")
    except Exception as e:
        print(f"Failed to send email to {receiver_emails}. Error: {e}")
